package org.fao.pims.persistence;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.ListIterator;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.fao.pims.model.*;



public class SpmDaoImpl extends BaseDao implements SpmDao {

	private static Log log = LogFactory.getLog(SpmDaoImpl.class);
	
	public SPModel getSpeciesInteractions(int taxonId) {
		
		SPModel model = null;
		
		PreparedStatement pstmt = null;
        ResultSet rset = null;
        Connection conn = null;
        List result = new ArrayList();
      
        try {
        	
        	conn = this.getDatasource().getConnection();         

        	String query = 
        		"SELECT pol.id, pol.lsid, pol.scientific_name, pol.rank, pol.higher_taxon" + 
        		"  FROM taxon_name crp, crop_pollinator cxp, taxon_name pol" +
        		" WHERE crp.id = " + 
        		"   AND crp.id = cxp.crop_taxon_name" +
        		"   AND pol.id = cxp.pollinator_taxon_name";
        	
            pstmt = conn.prepareStatement(query);
            
            rset = pstmt.executeQuery();    
            result = makeBeanList(resultsetToList(rset));
            
            rset.close();
            pstmt.close();
            conn.close();
            
        } 
        catch(SQLException e) {
            e.printStackTrace();
        }
        finally {
            try 
            { 
                if( ! conn.isClosed() )
                    conn.close(); 
                System.out.println("Connection closed");
            }
            catch(Exception e) {
                e.printStackTrace();
            }
        }
		
		return model;
		
		
	}
	
	/**
     * Preleva i dati del result set contenuti in un oggetto List e li salva in un 
     * bean appropriato, facendo corrispondere un bean a ciascun record.
     * Restituisce una lista di bean
     */
    protected List makeBeanList(List rows)
    {
        List beanList = new ArrayList();
         
        ListIterator rowIterator = rows.listIterator();
        int rowcounter = 0;
        while(rowIterator.hasNext())
        {
            rowcounter++;
            List fieldlist = (List)rowIterator.next();
            ListIterator fieldsIterator = fieldlist.listIterator();
            Object obj = null;
            String type = "";
            String columnName = "";
            InfoItem bean = new InfoItem();
            
            
            while(fieldsIterator.hasNext())
            {
                TableFieldHandler tfhandler = (TableFieldHandler)fieldsIterator.next();
                obj = tfhandler.getObject();
                type = tfhandler.getType();
                columnName = tfhandler.getColumnName().trim();
                // System.out.println(columnName);
                if( columnName.equalsIgnoreCase("ID") )
                {
                    bean.setId( ((Integer)obj).intValue() );
                    
                }                
                if( columnName.equalsIgnoreCase("TAXON") )
                {
                    bean.setTaxonId( ((Integer)obj).intValue() );
                    
                }
                if( columnName.equalsIgnoreCase("CONTENT") )
                {
                    bean.setContent( (String)obj );
                    
                }
                if( columnName.equalsIgnoreCase("CATEGORY") )
                {
                    bean.setCategory( (String)obj );
                    
                }
                if( columnName.equalsIgnoreCase("FORMAT") )
                {
                    bean.setFormat( (String)obj );
                    
                }                                               
                         
            }
            
            beanList.add(bean);
            
         }
        
        return beanList;
        
    }

}
